﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using XiAnAirport.BUS;
using XiAnAirport.Models;

namespace XiAnAirport.DAL
{
    public class StatisticsDAL
    {
        public List<Statistics> GetFaultCount(string starttime, string endtime, string ids)
        {
            string strsql1 = string.Empty;
            if (!string.IsNullOrEmpty(starttime))
            {
                strsql1 += " DATE(faultTime)>=DATE('" + starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                strsql1 += " DATE(faultTime)<=DATE('" + endtime + "') AND";
            }
            if (!string.IsNullOrEmpty(ids))
            {
                strsql1 += " elevatorID in(" + ids + ") AND";
            }
            if (strsql1.Length > 0)
            {
                strsql1 = " WHERE" + strsql1.Substring(0, strsql1.Length - 4);
            }
            string strSql = string.Format(@"SELECT
	                                            COUNT(1) AS sum,
	                                            elevatorName,
                                                elevatorNo,
                                                elevatorID 
                                            FROM
	                                            vi_fault{0}
                                            GROUP BY
	                                            elevatorID", strsql1);
            return SqlHelper<Statistics>.SqlList(strSql);
        }

        public string QueryFaultCountTime(string ids)
        {
            string strsql = string.Empty;
            if (!string.IsNullOrEmpty(ids))
            {
                strsql += "SELECT faultTime FROM vi_fault WHERE elevatorID in(" + ids + ") ORDER BY faultTime asc LIMIT 0,1";
            }
            else
            {
                strsql = "SELECT faultTime FROM vi_fault ORDER BY faultTime asc LIMIT 0,1";
            }
            //string strsql = "SELECT runsDate FROM vi_elevator_runs WHERE elevatorId IN(" + ids + ") ORDER BY runsDate asc LIMIT 0,1";
            return SqlHelper<ElevatorRuns>.SqlScalarString(strsql);
        }

        public List<Statistics> GetFaultCount()
        {
            string strSql = string.Format(@"SELECT
	                                            COUNT(1) AS sum,
	                                            elevatorName
                                            FROM
	                                            vi_fault
                                            WHERE outofservice = 0
                                            GROUP BY
	                                            elevatorName");
            return SqlHelper<Statistics>.SqlList(strSql);
        }

        public List<FaultRank> GetFaultRank(string starttime, string endtime)
        {
            string strSql = string.Format(@"SELECT
												a.faultContent,
												IFNULL(b.Sum, 0) AS sum
											FROM
												faultcontent a
											LEFT JOIN (
												SELECT
													COUNT(1) AS sum,
													faultCode
												FROM
													fault_report d
												LEFT JOIN elevator e ON d.elevatorID = e.id
												WHERE
													faultTime IS NOT NULL
												AND faultCode IS NOT NULL
												AND DATE(faultEndTime) >= DATE('{0}')
												AND DATE(faultEndTime) <= DATE('{1}')
												AND e.outofservice = 0
												GROUP BY
													faultCode
											) AS b ON a.id = b.faultCode", starttime, endtime);
            return SqlHelper<FaultRank>.SqlList(strSql);
        }

        public List<Statistics> GetRepairCount(string starttime, string endtime, string ids)
        {
            string sqlwhere = string.Empty;
            string sqlwhere1 = string.Empty;
            if (!string.IsNullOrEmpty(starttime))
            {
                sqlwhere += " DATE(preparationTime)>=DATE('" + starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sqlwhere += " DATE(preparationTime)<=DATE('" + endtime + "') AND";
            }
            if (!string.IsNullOrEmpty(ids))
            {
                sqlwhere += " elevatorId IN(" + ids + ")";
                sqlwhere1 = " WHERE a.id IN(" + ids + ")";
            }
            if (sqlwhere.Length > 0)
            {
                sqlwhere = " WHERE" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            string strSql = string.Format(@"SELECT
												a.elevatorName,
												IFNULL(b.Sum, 0) AS sum,
                                                a.elevatorNo
											FROM
												elevator a
											LEFT JOIN (
												SELECT
													COUNT(1) AS sum,
													elevatorName,
													elevatorId
												FROM
													vi_repairorderinfo{0}
												GROUP BY
													elevatorId
											) AS b ON a.id = b.elevatorId{1}", sqlwhere, sqlwhere1);
            return SqlHelper<Statistics>.SqlList(strSql);
        }

        public string GetRepairCountTime(string ids)
        {
            string strsql = string.Empty;
            if (!string.IsNullOrEmpty(ids))
            {
                strsql += "SELECT faultTime FROM vi_fault WHERE elevatorID in(" + ids + ") ORDER BY faultTime asc LIMIT 0,1";
            }
            else
            {
                strsql = "SELECT preparationTime FROM vi_repairorderinfo WHERE isDelete = 0 AND outofservice = 0 ORDER BY preparationTime ASC LIMIT 0,1";
            }
            return SqlHelper<ElevatorRuns>.SqlScalarString(strsql);
        }

        public int GetRunsCount(int elevatorid, string endtime, string starttime)
        {
            string strSql = string.Format(@"SELECT SUM(runs) as sum FROM elevator_runs WHERE elevatorId={0} AND DATE(runsDate)>=DATE('{1}') AND DATE(runsDate)<=DATE('{2}')", elevatorid, starttime, endtime);
            return SqlHelper<Statistics>.SqlScalar(strSql);
        }

        public int GetRunsMonth(int elevatorid, int year, int month)
        {
            string strSql = string.Format(@"SELECT SUM(runs) FROM vi_elevator_runs WHERE YEAR(runsDate)='{1}' AND MONTH(runsDate)='{2}' AND elevatorId={0}", elevatorid, year, month);
            return SqlHelper<Statistics>.SqlScalar(strSql);
        }

        public List<ElevatorRuns> GetRunsList(string elevatorids, string starttime, string endtime)
        {
            string sqlwhere = string.Empty;
            string sqlwhere1 = string.Empty;
            if (!string.IsNullOrEmpty(starttime))
            {
                sqlwhere += " DATE(runsDate)>=DATE('" + starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sqlwhere += " DATE(runsDate)<=DATE('" + endtime + "') AND";
            }
            if (!string.IsNullOrEmpty(elevatorids))
            {
                sqlwhere += " elevatorId IN(" + elevatorids + ") AND";
                sqlwhere1 = " WHERE elevator.id IN(" + elevatorids + ")";
            }
            if (sqlwhere.Length > 0)
            {
                sqlwhere = " WHERE" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            string strSql = string.Format(@"SELECT
                                                b.runs,
                                                elevator.elevatorName,
                                                elevator.elevatorNo,
                                                elevator.id
                                            FROM
                                                elevator
                                            LEFT JOIN(
                                                SELECT
                                                    SUM(runs) AS runs,
                                                    elevatorName,
                                                    elevatorId
                                                FROM
                                                    vi_elevator_runs{0}
                                                GROUP BY
                                                    elevatorId
                                            ) AS b ON elevator.id = b.elevatorId{1}", sqlwhere, sqlwhere1);


            return SqlHelper<ElevatorRuns>.SqlList(strSql);
        }

        public string QueryRunsListTime(string ids)
        {
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(ids))
            {
                sqlwhere += " WHERE elevatorId IN(" + ids + ")";
            }
            string strsql = string.Format("SELECT runsDate FROM vi_elevator_runs{0} ORDER BY runsDate asc LIMIT 0,1", sqlwhere);
            return SqlHelper<ElevatorRuns>.SqlScalarString(strsql);
        }

        public int GetRunsIds(string elevatorids, string starttime, string endtime)
        {
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(starttime))
            {
                sqlwhere += " DATE(runsDate)>=DATE('" + starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sqlwhere += " DATE(runsDate)<=DATE('" + endtime + "') AND";
            }
            if (!string.IsNullOrEmpty(elevatorids))
            {
                sqlwhere += " elevatorId IN(" + elevatorids + ") AND";
            }
            if (sqlwhere.Length > 0)
            {
                sqlwhere = " WHERE" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            string strSql = string.Format("SELECT SUM(runs) FROM vi_elevator_runs{0}", sqlwhere);

            return SqlHelper<ElevatorRuns>.SqlScalar(strSql);
        }

        public int GetElevatorIdsRunTimeCount(string elevatorids, string starttime, string endtime)
        {
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(starttime))
            {
                sqlwhere += " DATE(runDate)>=DATE('" + starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sqlwhere += " DATE(runDate)<=DATE('" + endtime + "') AND";
            }
            if (!string.IsNullOrEmpty(elevatorids))
            {
                sqlwhere += " elevatorId IN(" + elevatorids + ") AND";
            }
            if (sqlwhere.Length > 0)
            {
                sqlwhere = " WHERE" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            string strSql = string.Format("SELECT SUM(runTime) FROM vi_elevator_runstimes{0}", sqlwhere);

            return SqlHelper<ElevatorRuns>.SqlScalar(strSql);
        }

        public int GetRunTimeCount(int elevatorid, string endtime, string starttime)
        {
            string strSql = string.Format(@"SELECT SUM(runTime) FROM elevator_runstimes WHERE elevatorId={0} AND DATE(runDate)>=DATE('{1}') AND DATE(runDate)<=DATE('{2}')", elevatorid, starttime, endtime);
            return SqlHelper<Statistics>.SqlScalar(strSql);
        }

        public int GetRunTimeMonth(int elevatorid, int year, int month)
        {
            string strSql = string.Format(@"SELECT SUM(runTime) FROM elevator_runstimes WHERE YEAR(runDate)='{1}' AND MONTH(runDate)='{2}' AND elevatorId={0}", elevatorid, year, month);
            return SqlHelper<Statistics>.SqlScalar(strSql);
        }

        public List<ElevatorRunTime> GetRunTimeList(string elevatorids, string starttime, string endtime)
        {
            string sqlwhere = string.Empty;
            string sqlwhere1 = string.Empty;
            if (!string.IsNullOrEmpty(starttime))
            {
                sqlwhere += " DATE(runDate)>=DATE('" + starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sqlwhere += " DATE(runDate)<=DATE('" + endtime + "') AND";
            }
            if (!string.IsNullOrEmpty(elevatorids))
            {
                sqlwhere += " elevatorId IN(" + elevatorids + ") AND";
                sqlwhere1 = " WHERE elevator.id IN(" + elevatorids + ")";
            }
            if (sqlwhere.Length > 0)
            {
                sqlwhere = " WHERE" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            string strSql = string.Format(@"SELECT
	                                            (b.timesum / 3600) AS timesum,
	                                            elevator.elevatorName,
	                                            elevator.elevatorNo,
	                                            elevator.id
                                            FROM
	                                            elevator
                                            LEFT JOIN (
	                                            SELECT
		                                            SUM(runTime) AS timesum,
		                                            elevatorName,
		                                            elevatorId
	                                            FROM
		                                            vi_elevator_runstimes{0}
	                                            GROUP BY
		                                            elevatorId
                                            ) AS b ON elevator.id = b.elevatorId{1}", sqlwhere, sqlwhere1);
            return SqlHelper<ElevatorRunTime>.SqlList(strSql);
        }

        public string QueryRunTimeListTime(string ids)
        {
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(ids))
            {
                sqlwhere += " WHERE elevatorId IN(" + ids + ")";
            }
            string strsql = string.Format("SELECT runDate FROM vi_elevator_runstimes{0} ORDER BY runDate asc LIMIT 0,1", sqlwhere);
            return SqlHelper<ElevatorRuns>.SqlScalarString(strsql);
        }

        public int GetFaultTypeMonth(string faulttype, int year, int month)
        {
            int count = 0;
            try
            {
                string strSql = "select COUNT(1) FROM vi_repairorderinfo WHERE faultType=@faulttype AND isDelete=0 AND YEAR(preparationTime)=@year AND MONTH(preparationTime)=@month";
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@faulttype", faulttype);
                    mySqlCommand.Parameters.AddWithValue("@year", year);
                    mySqlCommand.Parameters.AddWithValue("@month", month);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }
            return count;
        }

        public List<FaultTypeTimes> GetFaultTypeList(string faulttype, string starttime, string endtime)
        {
            try
            {
                List<FaultTypeTimes> list = new List<FaultTypeTimes>();

                string sqlwhere = string.Empty;
                string sqlwhere1 = string.Empty;
                if (!string.IsNullOrEmpty(starttime))
                {
                    sqlwhere += " DATE(PreparationTime)>=DATE(@starttime) AND";
                }
                if (!string.IsNullOrEmpty(endtime))
                {
                    sqlwhere += " DATE(PreparationTime)<=DATE(@endtime) AND";
                }
                if (!string.IsNullOrEmpty(faulttype))
                {
                    string[] array = faulttype.Split(',');
                    string strfaulttype = string.Empty;
                    for (int i = 0; i < array.Length; i++)
                    {
                        strfaulttype += "'" + array[i] + "',";
                    }
                    strfaulttype = strfaulttype.Substring(0, strfaulttype.Length - 1);
                    sqlwhere += " faultType IN (" + strfaulttype + ") AND";
                    sqlwhere1 = " WHERE a.faultType IN (" + strfaulttype + ")";
                }
                if (sqlwhere.Length > 0)
                {
                    sqlwhere = " WHERE" + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }
                string strSql = string.Format(@"SELECT
	                                            a.faultType as faultname,
	                                            b.fault
                                            FROM
	                                            (
		                                            SELECT DISTINCT
			                                            (faultType)
		                                            FROM
			                                            fault_repairtype
	                                            ) AS a
                                            LEFT JOIN (
	                                            SELECT
		                                            COUNT(1) AS fault,
		                                            faultType
	                                            FROM
		                                            vi_repairorderinfo{0}
	                                            GROUP BY
		                                            faultType
                                            ) AS b ON a.faultType = b.faultType{1}", sqlwhere, sqlwhere1);

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@startTime", starttime);
                    mySqlCommand.Parameters.AddWithValue("@endTime", endtime);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<FaultTypeTimes>(dataTable);
                }
                return list;
            }
            catch
            {
                return null;
            }

        }

        public string QueryFaultTypeListTime(string faulttype)
        {
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(faulttype))
            {
                sqlwhere += " WHERE faultType IN(" + faulttype + ")";
            }
            string strSql = string.Format("select preparationTime FROM vi_repairorderinfo{0} ORDER BY preparationTime asc LIMIT 0,1", sqlwhere);

            return SqlHelper<FaultTypeTimes>.SqlScalarString(strSql);
        }

        public int GetElevatorFaultMonth(int elevatorid, int year, int month)
        {
            int count = 0;
            try
            {
                string strSql = "select COUNT(1) FROM vi_fault WHERE elevatorId=@elevatorid AND isDelete=0 AND YEAR(faultTime)=@year AND MONTH(faultTime)=@month";
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@elevatorid", elevatorid);
                    mySqlCommand.Parameters.AddWithValue("@year", year);
                    mySqlCommand.Parameters.AddWithValue("@month", month);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }
            return count;
        }

        public List<ElevatorFaultTimes> GetElevatorFaultList(string elevatorids, string starttime, string endtime)
        {
            try
            {
                List<ElevatorFaultTimes> list = new List<ElevatorFaultTimes>();
                string sqlwhere = string.Empty;
                string sqlwhere1 = string.Empty;
                if (!string.IsNullOrEmpty(starttime))
                {
                    sqlwhere += " DATE(faultTime)>=DATE(@starttime) AND";
                }
                if (!string.IsNullOrEmpty(endtime))
                {
                    sqlwhere += " DATE(faultTime)<=DATE(@endtime) AND";
                }
                if (!string.IsNullOrEmpty(elevatorids))
                {
                    sqlwhere += " elevatorID IN (" + elevatorids + ") AND";
                    sqlwhere1 = " WHERE elevator.id IN (" + elevatorids + ")";
                }
                if (sqlwhere.Length > 0)
                {
                    sqlwhere = " WHERE" + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }
                string strSql = string.Format(@"SELECT
	                                            b.fault,
	                                            elevator.elevatorName,
	                                            elevator.elevatorNo,
	                                            elevator.id
                                            FROM
	                                            elevator
                                            LEFT JOIN (
	                                            SELECT
		                                            COUNT(1) AS fault,
		                                            elevatorName,
		                                            elevatorID
	                                            FROM
		                                            vi_fault{0}
	                                            GROUP BY
		                                            elevatorID
                                            ) AS b ON elevator.id = b.elevatorID{1}", sqlwhere, sqlwhere1);
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@startTime", starttime);
                    mySqlCommand.Parameters.AddWithValue("@endTime", endtime);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<ElevatorFaultTimes>(dataTable);
                }
                return list;
            }
            catch
            {
                return null;
            }

        }

        public string QueryElevatorFaultListTime(string ids)
        {
            string strsql = string.Empty;
            if (!string.IsNullOrEmpty(ids))
            {
                strsql = "SELECT faultTime FROM vi_fault WHERE elevatorID IN(" + ids + ") ORDER BY faultTime asc LIMIT 0,1";
            }
            else
            {
                strsql = "SELECT faultTime FROM vi_fault ORDER BY faultTime asc LIMIT 0,1";
            }

            return SqlHelper<ElevatorRuns>.SqlScalarString(strsql);
        }

        public int GetFaultSum(int elevatorid, string endtime, string starttime)
        {
            int count = 0;
            try
            {
                string strSql = "select COUNT(1) FROM vi_fault WHERE elevatorId=@elevatorid AND DATE(faultTime)<=DATE(@endtime) AND DATE(faultTime)>=DATE(@starttime)";
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@elevatorid", elevatorid);
                    mySqlCommand.Parameters.AddWithValue("@startTime", starttime);
                    mySqlCommand.Parameters.AddWithValue("@endtime", endtime);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }
            return count;
        }

        public int GetFaultListSum(string elevatorids, string endtime, string starttime)
        {
            int count = 0;
            try
            {
                string strSql = string.Format(@"select COUNT(1) FROM vi_fault WHERE elevatorId in ({0})", elevatorids);
                if (!string.IsNullOrEmpty(starttime))
                {
                    strSql += " AND DATE(faultTime)>=DATE(@starttime)";
                }
                if (!string.IsNullOrEmpty(endtime))
                {
                    strSql += " AND DATE(faultTime)<=DATE(@endtime)";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@startTime", starttime);
                    mySqlCommand.Parameters.AddWithValue("@endtime", endtime);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }
            return count;
        }

        public List<T> ToList<T>(DataTable dt) where T : class, new()
        {
            List<T> list = new List<T>();
            //确认参数有效,若无效则返回Null
            if (dt == null)
                return list;
            else if (dt.Rows.Count == 0)
                return list;
            Dictionary<string, string> dicField = new Dictionary<string, string>();
            Dictionary<string, string> dicProperty = new Dictionary<string, string>();
            Type type = typeof(T);

            //创建字段字典，方便查找字段名
            foreach (var item in type.GetFields())
            {
                dicField.Add(item.Name.ToLower(), item.Name);
            }
            //创建属性字典，方便查找属性名
            foreach (var item in type.GetProperties())
            {
                dicProperty.Add(item.Name.ToLower(), item.Name);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string memberKey = dt.Columns[j].ColumnName.ToLower();

                    //字段赋值
                    if (dicField.ContainsKey(memberKey))
                    {
                        FieldInfo theField = type.GetField(dicField[memberKey]);
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theField.FieldType;
                            //dbValue = dbValue.ChangeType(memberType);
                            dbValue = Convert.ChangeType(dbValue, memberType);
                        }
                        theField.SetValue(_t, dbValue);
                    }
                    //属性赋值
                    if (dicProperty.ContainsKey(memberKey))
                    {
                        PropertyInfo theProperty = type.GetProperty(dicProperty[memberKey]);
                        if (!theProperty.CanWrite) continue;
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theProperty.PropertyType;
                            if (memberType.IsGenericType && memberType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                            {
                                NullableConverter newNullableConverter = new NullableConverter(memberType);
                                dbValue = newNullableConverter.ConvertFrom(dbValue);
                            }
                            else
                            {
                                dbValue = Convert.ChangeType(dbValue, memberType);
                            }
                        }
                        theProperty.SetValue(_t, dbValue, null);
                    }
                }
                list.Add(_t);
            }
            return list;
        }
    }
}
